/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Models.Banner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Hse7en
 */
public class BannerDAO {

    public ArrayList<Banner> getEnabledBanner() {
        Connection conn = null;
        ArrayList<Banner> list = new ArrayList<Banner>();
        try {
            conn = (Connection) DBConnection.myConnection();
            PreparedStatement stm = conn.prepareStatement("select * from Banner Where Status='true'");
            ResultSet rs = stm.executeQuery();
            Banner b = null;
            while (rs.next()) {
                b = new Banner();
                b.setTitle(rs.getString(2).trim());
                b.setImage(rs.getString(3).trim());
                b.setLink(rs.getString(4).trim());
                b.setDescription(rs.getString(5).trim());
                b.setStatus(rs.getBoolean(6));
                list.add(b);
            }
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public ArrayList<Banner> getAllBanner() {
        Connection conn = null;
        ArrayList<Banner> list = new ArrayList<Banner>();
        try {
            conn = (Connection) DBConnection.myConnection();
            PreparedStatement stm = conn.prepareStatement("select * from Banner");
            ResultSet rs = stm.executeQuery();
            Banner b = null;
            while (rs.next()) {
                b = new Banner();
                b.setId(rs.getInt(1));
                b.setTitle(rs.getString(2).trim());
                b.setImage(rs.getString(3).trim());
                b.setLink(rs.getString(4).trim());
                b.setDescription(rs.getString(5).trim());
                b.setStatus(rs.getBoolean(6));
                list.add(b);
            }
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addBanner(Banner banner) {
        int result = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("Insert into Banner values(?,?,?,?,?)");
            stm.setString(1, banner.getTitle());
            stm.setString(2, banner.getImage());
            stm.setString(3, banner.getLink());
            stm.setString(4, banner.getDescription());
            stm.setBoolean(5, banner.getStatus());
            result = stm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public void deleteBannerByID(int id) {
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("DELETE FROM Banner WHERE ID = ?");
            stm.setInt(1, id);
            stm.executeUpdate();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int statisticBanner() {
        int total = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("Select count(*) from Banner");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public static void editBanner(Banner banner) throws Exception {
        Connection con = (Connection) DBConnection.myConnection();
        String sql = "update Banner set Title=?, Image=?, Link=?, Description=?, Status=? where ID=?";
        PreparedStatement stm = con.prepareStatement(sql);
        stm.setString(1, banner.getTitle());
        stm.setString(2, banner.getImage());
        stm.setString(3, banner.getLink());
        stm.setString(4, banner.getDescription());
        stm.setBoolean(5, banner.getStatus());
        stm.executeUpdate();
        con.close();
    }
}
